#!/usr/bin/env bash
#抽取DWD层转速异常点数据到DWS层dws_vehicle_rotation_abnormal_statistics_pdi表

CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}
hive_db_source=${hive_db_source}

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_trip
else
  hive_db=${hive_db}
fi

if [ -z "${hive_db_source}" ];then
  hive_db_source=dongfeng
else
  hive_db_source=${hive_db_source}
fi


if [ -z "$1" ];then
  part_time=$(date -d "1 days ago" +%Y%m%d)
else
  part_time=$1
fi

#定义参数
START_TIME=`date +%s`
#hive_db=$1
part_time=$1

#生成hql语句
sql_suffix=$(cat <<EOF
insert overwrite table ${hive_db}.dws_vehicle_rotation_abnormal_statistics_pdi partition (day=${part_time})
select concat(cast(reverse(tid) as string),'|',cast(route_start_time as string)) as key
,tid,trip_id,route_start_time,route_end_time,trip_mileage
		,array(point_abnormal_info) as point_abnormal_info
from (
select trip.tid
       ,trip.trip_id
       ,trip.route_start_time
       ,trip.route_end_time
       ,trip.trip_mileage/1000 as trip_mileage
       ,concat_ws(',',collect_set(locationpoint.point_abnormal_info)) as point_abnormal_info
  from
(select tid,tripid
 	,concat('{\"gpsTime\":\"',from_unixtime(cast(cast(nvl(gpsTime,0) as bigint)/1000 as bigint),'yyyy/MM/dd HH:mm:ss'),'\",\"speed":\"',speed/100,'\",\"rotation":\"',rotation/100,'\"}') as point_abnormal_info
 from ${hive_db}.dwc_vehicle_track_protocol
 where speed > 60*100 and rotation < 800*100 and day = ${part_time}) locationpoint
  join
(select tid,trip_id,route_start_time,route_end_time,trip_mileage from ${hive_db}.trip_statistics
 where day = ${part_time}) trip
    on locationpoint.tid = trip.tid
   and locationpoint.tripid = trip.trip_id
  group by trip.tid
       ,trip.trip_id
       ,trip.route_start_time
       ,trip.route_end_time
	   ,trip.trip_mileage
 ) as src
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  ${CUR_DIR}/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入dws_vehicle_rotation_abnormal_statistics_pdi的SQL为：${sql}"

#执行语句
hive -e "${sql}"

if [[ $? -ne 0 ]];then
  echo "导入dws_vehicle_rotation_abnormal_statistics_pdi失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入dws_vehicle_rotation_abnormal_statistics_pdi成功，耗时$((`date +%s`-START_TIME))秒..."
fi